
package vehicleService;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import vehicleEntity.PurchasedVehicleEntity;

/**
 * 
 *  @author Sashiraj Dhanarajan
 *
 */

public class PurchaseService {
	private Connection connection;
	private PreparedStatement statement;

	/**
	 * This method establishes the database connection 
	 * @pre Network should be connected
	 * @post Connects with the Sql database
	 */
	public PurchaseService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

	}

	/**
	 * This method returns a list of all purchased vehicles from PurchasedVehicles table
	 * @param vehicleId is vehicle identification number
	 * @return Returns the list of purchased vehicle
	 * @throws SQLException 
	 * @pre Should be connected to the databse
	 * @post Fetches purchased vehicle details from the database
	 */
	public ResultSet fetchPurchasedVehiclesDetails() throws SQLException{
		ResultSet rs = null;
		String query = "select pv.vehicleId,pv.purchasedDate,pv.manufDate,pv.vehicleIns,pv.amount,v.RegNo,v.VIN,v.vehicleType,v.vehicleClass,v.VehicleName from purchased_vehicle pv inner join vehicle v  where pv.vehicleId = v.vehicleId;";
				
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}

	/**
	 * This method fetches the details of the vehicle with given vehicle ID from
	 * Purchases vehicle table
	 * @param VehicleId
	 * @return Return the result set containing purchased vehicle detail
	 * @throws SQLException 
	 * @pre Should be connected to the database and vehicleId should be entered
	 * @post fetch the vehicle details based on the vehicleId
	 */
	public ResultSet fetchPurchasedVehicleDetail(int vehicleId) throws SQLException{
		ResultSet rs = null;
		String query = "select pv.vehicleId,pv.purchasedDate,pv.manufDate,pv.vehicleIns,pv.amount,v.RegNo,v.VIN,v.vehicleType,v.vehicleClass,v.VehicleName from purchased_vehicle pv join vehicle v where pv.vehicleId = v.vehicleId and pv.vehicleId=?;";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, vehicleId);
			//statement = connection.prepareStatement(query);
			System.out.println("Result set is " + rs);
			rs = statement.executeQuery();
			System.out.println("Result set is " + rs); 
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}

		return rs;

	}


	/**
	 * This method saves the updated value of the purchased vehicle in purchase table and vehicle table
	 * @param purchasedVehicle
	 * @return Returns the resultset with the updated value
	 * @throws SQLException 
	 * @pre Vehicle details should be fetched for the given vehicleId 
	 * @post Updates the edited values into the vehicle database
	 */
	public ResultSet saveUpdatedValue(PurchasedVehicleEntity purchasedVehicle) throws SQLException{
		ResultSet rs = null;
		String updatequery1 = "update vehicle set RegNo = ? , VIN = ? , vehicleType = ?,vehicleClass = ? , VehicleName = ? where vehicleId = ? ;";

		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery1);
			statement.setString(1, purchasedVehicle.getRegNo());
			statement.setString(2, purchasedVehicle.getVin());
			statement.setString(3, purchasedVehicle.getVehicleType());
			statement.setString(4, purchasedVehicle.getVehicleClass());
			statement.setString(5, purchasedVehicle.getVehicleName());
			statement.setInt(6, purchasedVehicle.getVehicleId());
			statement.executeUpdate();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * 
	 * @param purchasedVehicle
	 * @return Returns the resultset with the updated value
	 * @throws SQLException
	 * @pre Vehicle details should be fetched for the given vehicleId 
	 * @post Updates the edited values into the purchased_vehicle database
	 */
	public ResultSet saveUpdatedValue2(PurchasedVehicleEntity purchasedVehicle) throws SQLException{
		ResultSet rs = null;
		String updatequery1 = "update purchased_vehicle set purchasedDate = ? , manufDate = ? , vehicleIns = ?, amount = ? where vehicleId = ? ;";

		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery1);
			statement.setDate(1, new Date(purchasedVehicle.getPurchasedDate().getTime()));
			statement.setDate(2,new Date(purchasedVehicle.getManufDate().getTime()));
			statement.setDouble(3,purchasedVehicle.getVehicleIns());
			statement.setDouble(4, purchasedVehicle.getAmount());
			statement.setInt(5,purchasedVehicle.getVehicleId());

			statement.executeUpdate();

		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}


	/**
	 * This method adds new vehicle to the vehicle table and purchased vehicle table
	 * @param purchasedVehicle
	 * @return Returns the result set with newly entered vales
	 * @throws SQLException 
	 * @pre All fields in the add form should be given the entry
	 * @post Insert the newly entered values into the vehicle database
	 */

	public ResultSet addVehicle(PurchasedVehicleEntity purchasedVehicle) throws SQLException{

		ResultSet rs = null;
		String query1 = "insert into vehicle values(null,?,?,?,?,?)";
		String query2 = "select last_insert_id() as last_id from vehicle";

		try
		{
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query1);
			statement.setString(1, purchasedVehicle.getRegNo());
			statement.setString(2, purchasedVehicle.getVin());
			statement.setString(3, purchasedVehicle.getVehicleType());
			statement.setString(4, purchasedVehicle.getVehicleClass() );
			statement.setString(5, purchasedVehicle.getVehicleName());
			statement.executeUpdate();
			statement = connection.prepareStatement(query2);
			rs = statement.executeQuery();

		}
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	/*
	 * This method insert the newly enetered vales into the purchased_vehicle
	 * @pre Newly entered details should be first inserted into the vehicle database
	 * @post Newly enetered values is entered into the purchased_vehicle database
	 */

	public void addPurchasedVehicle(PurchasedVehicleEntity purchasedVehicle) throws SQLException { 

		String query ="insert into purchased_vehicle values(?,?,?,?,?)";

		try
		{
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, purchasedVehicle.getVehicleId());
			statement.setDate(2, new Date(purchasedVehicle.getPurchasedDate().getTime()));
			statement.setDate(3,new Date(purchasedVehicle.getManufDate().getTime()));
			statement.setDouble(4, purchasedVehicle.getVehicleIns());
			statement.setDouble(5, purchasedVehicle.getAmount());

			statement.executeUpdate();

		}
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
	}

	/**
	 * This methos commits all the transactions
	 * @throws SQLException
	 */
	public void transactionCommit() throws SQLException{ 
		if(connection!=null){
			try {
				connection.commit();
			} catch (SQLException e) {
				if(connection != null){
					try {
						connection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
						throw e1;
					}
				}
				e.printStackTrace();
				throw e;
			}
			finally{
				try {
					connection.setAutoCommit(true);
				}
				catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
	}

	/**
	 * This method Roll backs 
	 * @throws SQLException
	 */
	public void transactionRollBack() throws SQLException{

		try {
			if(connection != null){
				connection.rollback();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}

		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
	}

	/**
	 * This method used is used for selecting the vehicle details based on the VIN nuber
	 * @param vin
	 * @return
	 * @throws SQLException
	 * @pre Should give the exiting VIN number
	 * @post If values found for the given VIN number then it throws exception.
	 */

	public ResultSet checkVIN(String vin) throws SQLException {
		ResultSet rs = null;
		String query = "select * from vehicle where vin = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vin);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	/**
	 * This method is used for inserting Vehicle specification into the database
	 * @param VehicleId
	 * @param spec
	 * @throws SQLException
	 * @pre vehicle class and Vehicle type should be selected
	 * @post Insert the values entered into  database
	 */
	public void addVehicleSpecification(int VehicleId, String spec) throws SQLException {
		String query ="insert into vehicle_specification values(?,?)";

		try
		{
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setInt(1, VehicleId);
			statement.setString(2, spec);
			statement.executeUpdate();

		}
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

	}
	/**
	 * This method is used for selecting the vehicle specification
	 * @param vehicleId
	 * @return
	 * @throws SQLException
	 * @pre The vehicleId should exist
	 * @post Fetches all details based on vehicle
	 */

	public ResultSet fetchVehicleSpec(int vehicleId) throws SQLException {
		ResultSet rs = null;
		String query = "select * from vehicle_specification where vehicle = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, vehicleId);
			rs = statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	/**
	 * This method delete the existing vehicle specification for the particular vehicleId
	 * @param purchasedVehicle
	 * @throws SQLException
	 * @pre This vehicle specification should be changed
	 * @post deletes the specfication for the given vehicle and updates with new entry in the purchase logic.
	 */

	public void saveUpdatedValue3(PurchasedVehicleEntity purchasedVehicle) throws SQLException {
		String updatequery1 = "delete from vehicle_specification where vehicle = ?";

		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(updatequery1);
			statement.setInt(1, purchasedVehicle.getVehicleId());
			statement.executeUpdate();

		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

	}

}
